package com.sinopac.model.TokenManagement;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;

import com.sinopac.entity.TokenManagement.CustVO;
import com.sinopac.entity.TokenManagement.TokenVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.IdUtil;

public class TokenManagementModel extends ModelCore{
	
	public TokenVO getTokenList(String token_esn){
		DataAccessManager dam = new DataAccessManager();
		List<CustVO> custList = new ArrayList<CustVO>();
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		TokenVO tokenVO = new TokenVO();
		CustVO custVO = new CustVO();
		Map<String,String> token_statusMap = new HashMap<String, String>();
		Map<String,String> otpStatus = new HashMap<String, String>();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		token_statusMap.put("00", "未啟用");
		token_statusMap.put("01", "正常");
		token_statusMap.put("02", "已暫禁");
		token_statusMap.put("03", "已註銷");
		token_statusMap.put("04", "異動中，待覆核");
		token_statusMap.put("05", "申請中，待覆核");
		otpStatus.put("00", "未啟用");
		otpStatus.put("01", "正常");
		otpStatus.put("02", "已暫禁");
		otpStatus.put("03", "已註銷");
		String sql1 = "select * from Tokens t left join Token_Customer tc on t.token_esn = tc.token_esn where t.token_esn = :token_esn ";
		dam.setQueryString(sql1);
		dam.setObject("token_esn", token_esn);
		try {
			list = dam.exeQuery();
			if(!list.isEmpty()){
				Map<String,Object> tokenMap = list.get(0);
				tokenVO.setToken_esn((String)tokenMap.get("token_esn"));
				dam = new DataAccessManager();
				StringBuffer sql_sb = new StringBuffer();
				sql_sb.append("select t.token_status, ");
				sql_sb.append("       \"error_msg\" = case ");
//				sql_sb.append("                       when tc.related_to is not null then '04' ");
				sql_sb.append("                       when j.job_code = '1000' then '05' ");
				sql_sb.append("                       when j.job_code is null then '' ");
				sql_sb.append("                     end ");
				sql_sb.append("from   tokens as t ");
				sql_sb.append("       left join token_customer as tc ");
				sql_sb.append("              on t.token_esn = tc.token_esn ");
				sql_sb.append("       left join job_queues as j ");
				sql_sb.append("              on t.token_esn = j.token_esn ");
				sql_sb.append("where  t.token_esn = :token_esn ");
				dam.setQueryString(sql_sb.toString());
				dam.setObject("token_esn", token_esn);
				List<Map<String,Object>> token_list = dam.exeQuery();
				Map<String,Object> data_set = token_list.get(0);
				String token_status = (String)tokenMap.get("token_status");
				tokenVO.setToken_status(token_status);
//				String token_status = (String)data_set.get("error_msg");
//				tokenVO.setToken_status(StringUtils.isBlank(token_status)?(String)tokenMap.get("token_status"):token_status);
				if(StringUtils.equals("00", tokenVO.getToken_status()) && tokenMap.get("related_to")==null){
					tokenVO.setToken_cn("未配發");
				}else{
					tokenVO.setToken_cn(token_statusMap.get(tokenVO.getToken_status()));
				}
				tokenVO.setRelated_to((String)tokenMap.get("related_to"));
				tokenVO.setReceipt_printed_time(tokenMap.get("receipt_printed_time")==null?"":sf.format((Timestamp)tokenMap.get("receipt_printed_time")));
				tokenVO.setUpdated_time(tokenMap.get("updated_time")==null?"":sf.format((Timestamp)tokenMap.get("updated_time")));
				if(tokenMap.get("related_to")!=null){
					String is_related_to_group = "";
					if(tokenMap.get("is_related_to_group")!=null){
						is_related_to_group = StringUtils.trimToEmpty((String)tokenMap.get("is_related_to_group"));
					}
					dam = new DataAccessManager();
					StringBuffer sql2 = new StringBuffer();
					sql2.append("select c.* from Customers c inner join Token_Customer t ");
					if(is_related_to_group.equals("0")){
						sql2.append("on c.customer_id = t.related_to ");
					}else if(is_related_to_group.equals("1")){
						sql2.append("on c.group_id = t.related_to ");
					}
					sql2.append("where t.token_esn = :token_esn ");
					dam.setQueryString(sql2.toString());
					dam.setObject("token_esn", token_esn);
					List<Map<String,Object>> list2 = new ArrayList<Map<String,Object>>();
					list2 = dam.exeQuery();
					if(!list2.isEmpty()){
						for(Map<String,Object> map:list2){
							custVO = new CustVO();
							custVO.setCustomer_id((String)map.get("customer_id"));
							custVO.setCustomer_name((String)map.get("customer_name"));
							custVO.setCustomer_phone((String)map.get("customer_phone"));
							custVO.setAo_code((String)map.get("AO_code"));
							custVO.setCustomer_address((String)map.get("customer_address"));
							custVO.setCustomer_contact((String)map.get("customer_contact"));
							custVO.setGroup_id((String)map.get("group_id"));
							custVO.setIs_group((String)map.get("is_group"));
							custVO.setIs_group_main((String)map.get("is_group_main"));
							String group_cn = "";
							if(StringUtils.equals(custVO.getIs_group(),"0")){
								group_cn = "否";
							}else{
								if(StringUtils.equals("0", custVO.getIs_group_main())){
									group_cn = "子";
								}else{
									group_cn = "主";
								}
							}
							custVO.setGroup_cn(group_cn);
							custVO.setOtp_status(otpStatus.get((String)map.get("otp_status")));
							custVO.setCreated_time(map.get("created_time") != null?sf.format((Timestamp)map.get("created_time")):"");
							custVO.setUpdated_time(map.get("updated_time") != null?sf.format((Timestamp)map.get("updated_time")):"");
							custList.add(custVO);
						}
						tokenVO.setCustVO(custList);
					}
				}else{
					List<Map<String,Object>> list2 = new ArrayList<Map<String,Object>>();
					dam = new DataAccessManager();
					StringBuffer sql = new StringBuffer();
					sql.append("select c.* from Customers c inner join Job_queues j ");
					sql.append("on j.customer_id = c.customer_id where j.token_esn = :token_esn ");
					dam.setObject("token_esn", token_esn);
					dam.setQueryString(sql.toString());
					list2 = dam.exeQuery();
					if(!list2.isEmpty()){
						Map<String,Object> custMap = list2.get(0);
						String is_group = custMap.get("is_group").toString();
						if(is_group.equals("0")){
							custVO = new CustVO();
							custVO.setCustomer_id((String)custMap.get("customer_id"));
							custVO.setCustomer_name((String)custMap.get("customer_name"));
							custVO.setCustomer_phone((String)custMap.get("customer_phone"));
							custVO.setAo_code((String)custMap.get("AO_code"));
							custVO.setCustomer_address((String)custMap.get("customer_address"));
							custVO.setCustomer_contact((String)custMap.get("customer_contact"));
							custVO.setGroup_id((String)custMap.get("group_id"));
							custVO.setIs_group((String)custMap.get("is_group"));
							custVO.setIs_group_main((String)custMap.get("is_group_main"));
							String group_cn = "";
							if(StringUtils.equals(custVO.getIs_group(),"0")){
								group_cn = "否";
							}else{
								if(StringUtils.equals("0", custVO.getIs_group_main())){
									group_cn = "子";
								}else{
									group_cn = "主";
								}
							}
							custVO.setGroup_cn(group_cn);
							custVO.setOtp_status(otpStatus.get((String)custMap.get("otp_status")));
							custVO.setCreated_time(custMap.get("created_time") != null?sf.format((Timestamp)custMap.get("created_time")):"");
							custVO.setUpdated_time(custMap.get("updated_time") != null?sf.format((Timestamp)custMap.get("updated_time")):"");
							custList.add(custVO);
						}else if(is_group.equals("1")){
							dam = new DataAccessManager();
							StringBuffer sql2 = new StringBuffer();
							sql2.append("select * from Customers where group_id = :groupId");
							String groupId = custMap.get("group_id").toString();
							dam.setObject("groupId", groupId);
							dam.setQueryString(sql2.toString());
							List<Map<String,Object>> groupLlist = dam.exeQuery();
							if(!groupLlist.isEmpty()){
								for(Map<String,Object> map:groupLlist){
									custVO = new CustVO();
									custVO.setCustomer_id((String)map.get("customer_id"));
									custVO.setCustomer_name((String)map.get("customer_name"));
									custVO.setCustomer_phone((String)map.get("customer_phone"));
									custVO.setAo_code((String)map.get("AO_code"));
									custVO.setCustomer_address((String)map.get("customer_address"));
									custVO.setCustomer_contact((String)map.get("customer_contact"));
									custVO.setGroup_id((String)map.get("group_id"));
									custVO.setIs_group((String)map.get("is_group"));
									custVO.setIs_group_main((String)map.get("is_group_main"));
									String group_cn = "";
									if(StringUtils.equals(custVO.getIs_group(),"0")){
										group_cn = "否";
									}else{
										if(StringUtils.equals("0", custVO.getIs_group_main())){
											group_cn = "子";
										}else{
											group_cn = "主";
										}
									}
									custVO.setGroup_cn(group_cn);
									custVO.setOtp_status(otpStatus.get((String)map.get("otp_status")));
									custVO.setCreated_time(map.get("created_time") != null?sf.format((Timestamp)map.get("created_time")):"");
									custVO.setUpdated_time(map.get("updated_time") != null?sf.format((Timestamp)map.get("updated_time")):"");
									custList.add(custVO);
								}
//								tokenVO.setCustVO(custList);
							}
						}
						tokenVO.setCustVO(custList);					
					}				
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tokenVO;
	}
	
	public Boolean transactionToken(String token_esn,String actionName,String userName){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		StringBuffer  sql_sb = new StringBuffer();
		sql_sb.append("select tc.*, ");
		sql_sb.append("       t.token_status ");
		sql_sb.append("from   tokens as t ");
		sql_sb.append("       inner join token_customer as tc ");
		sql_sb.append("               on t.token_esn = tc.token_esn ");
		sql_sb.append("where  t.token_esn = :token_esn ");
		dam.setQueryString(sql_sb.toString());
		dam.setObject("token_esn", token_esn);
		try {
			List<Map<String,Object>> job_list = dam.exeQuery();
			if(!job_list.isEmpty()){
				for(Map<String,Object> map:job_list){
					if(StringUtils.equals("0", (String)map.get("is_related_to_group"))){
						if(StringUtils.equals(actionName, "pauseToken")){
							insertJobQs((String)map.get("related_to"), token_esn, userName, "1002",(String)map.get("token_status"));
						}else if(StringUtils.equals(actionName, "unstallToken")){
							insertJobQs((String)map.get("related_to"), token_esn, userName, "1003",(String)map.get("token_status"));
						}else if(StringUtils.equals(actionName, "invalidToken")){
							insertJobQs((String)map.get("related_to"), token_esn, userName, "1004",(String)map.get("token_status"));
						}else if(StringUtils.equals(actionName, "enableToken")){
							insertJobQs((String)map.get("related_to"), token_esn, userName, "1001",(String)map.get("token_status"));
						}
					}else{
						dam = new DataAccessManager();
						StringBuffer sql = new StringBuffer();
						sql.append("select c.customer_id, ");
						sql.append("       c.group_id ");
						sql.append("from   token_customer as tc ");
						sql.append("       inner join customers as c ");
						sql.append("               on tc.related_to = c.group_id ");
						sql.append("where  c.is_group_main = '1' ");
						sql.append("       and tc.token_esn = :token_esn ");
						dam.setQueryString(sql.toString());
						dam.setObject("token_esn",token_esn);
						List<Map<String,Object>> cust_list = dam.exeQuery();
						if(!cust_list.isEmpty()){
							Map<String,Object> map1 = cust_list.get(0);
							if(StringUtils.equals(actionName, "pauseToken")){
								insertJobQs_group((String)map1.get("customer_id"), token_esn,
												userName, "1002",(String)map.get("token_status"),
												(String)map1.get("group_id"));
							}else if(StringUtils.equals(actionName, "unstallToken")){
								insertJobQs_group((String)map1.get("customer_id"), token_esn,
												userName, "1003",(String)map.get("token_status"),
												(String)map1.get("group_id"));
							}else if(StringUtils.equals(actionName, "invalidToken")){
								insertJobQs_group((String)map1.get("customer_id"), token_esn,
												userName, "1004",(String)map.get("token_status"),
												(String)map1.get("group_id"));
							}else if(StringUtils.equals(actionName, "enableToken")){
								insertJobQs_group((String)map1.get("customer_id"), token_esn,
												userName, "1001",(String)map.get("token_status"),
												(String)map1.get("group_id"));
							}
						}
					}
				}
			}
			dam = new DataAccessManager();
			StringBuffer sql = new StringBuffer();
			sql.append("update Tokens set token_status = :token_status, ");
			sql.append("last_update_job = :last_update_job, ");
			sql.append("updated_time = getdate() ");
			sql.append("where token_esn = :token_esn ");
			dam.setQueryString(sql.toString());
			dam.setObject("token_esn", token_esn);
			if(StringUtils.equals(actionName, "pauseToken")){
				dam.setObject("token_status", "04");
				dam.setObject("last_update_job", "1002");
			}else if(StringUtils.equals(actionName, "unstallToken")){
				dam.setObject("token_status", "04");
				dam.setObject("last_update_job", "1003");
			}else if(StringUtils.equals(actionName, "invalidToken")){
				dam.setObject("token_status", "04");
				dam.setObject("last_update_job", "1004");
			}else if(StringUtils.equals(actionName, "enableToken")){
				dam.setObject("token_status", "04");
				dam.setObject("last_update_job", "1001");
			}
			flag = dam.exeUpdate()>0;
//			if(StringUtils.equals(actionName, "invalidToken")){
//				dam = new DataAccessManager();
//				sql = new StringBuffer();
//				sql.append("delete from Token_Customer where token_esn = :token_esn");
//				dam.setObject("token_esn", token_esn);
//				dam.setQueryString(sql.toString());
//				dam.exeDelete();
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public String chkCustId(String custId,String token_esn){
		String error_msg = "不存在，請重新輸入正確客戶ID";
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append("select \"error_msg\" = case ");
		sql_sb.append("                       when c.otp_status in ( '01', '02' ) then ");
		sql_sb.append("                       'OTP狀態須為未啟用或已註銷，請重新輸入正確客戶ID' ");
		sql_sb.append("                       when j.job_code = '1000' then '正在申請待主管覆核' ");
		sql_sb.append("                       when tc.token_esn is not null ");
		sql_sb.append("                            and c.is_group = '1' ");
		sql_sb.append("                            and c.is_group_main = '1' then '已配發過，請重新輸入正確客戶ID' ");
		sql_sb.append("                       when tc.token_esn is not null ");
		sql_sb.append("                            and c.is_group = '0' ");
		sql_sb.append("                            and c.is_group_main = '0' then '已配發過，請重新輸入正確客戶ID' ");
		sql_sb.append("                       when tc.token_esn is not null ");
		sql_sb.append("                            and c.is_group = '1' ");
		sql_sb.append("                            and c.is_group_main = '0' then ");
		sql_sb.append("                       '已配發過且不為主帳戶，請重新輸入正確客戶ID' ");
		sql_sb.append("                       when c.is_group_main = '0' ");
		sql_sb.append("                            and c.is_group = '1' then '不為主帳戶無法申請，請重新輸入正確客戶ID' ");
		sql_sb.append("                       when tc.token_esn is null ");
		sql_sb.append("                            and j.job_code is null then '' ");
		sql_sb.append("                     end ");
		sql_sb.append("from   customers as c ");
		sql_sb.append("       left join token_customer as tc ");
		sql_sb.append("              on ( c.customer_id = tc.related_to ");
		sql_sb.append("                    or c.group_id = tc.related_to ) ");
		sql_sb.append("       left join job_queues as j ");
		sql_sb.append("              on c.customer_id = j.customer_id ");
		sql_sb.append("where  c.customer_id = :custId ");
		dam.setQueryString(sql_sb.toString());
		dam.setObject("custId", custId);
		try {
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				Map<String,Object> map = list.get(0);
				return (String)map.get("error_msg");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return error_msg;
	}
	
//	public String chkCustId(String custId,String token_esn){
//		String message = "";
//		DataAccessManager dam = new DataAccessManager();
//		StringBuffer sql = new StringBuffer("select * from Customers where customer_id = :custId ");
//		dam.setQueryString(sql.toString());
//		dam.setObject("custId", custId);
//		try {
//			List<Map<String,Object>> custList = new ArrayList<Map<String,Object>>();
//			custList = dam.exeQuery();
//			if(!custList.isEmpty()){
////				while(true){
//				dam = new DataAccessManager();
//				sql = new StringBuffer();
//				sql.append("select * from Customers where customer_id = :custId ");
//				sql.append("and otp_status in ('00','03') ");
//				dam.setQueryString(sql.toString());
//				dam.setObject("custId", custId);
//				List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
//				list = dam.exeQuery();
//				if(list.isEmpty()){
//					return "OTP狀態須為未啟用或已註銷，請重新輸入正確客戶ID";
////					break;
//				}
//				Map<String,Object> map = list.get(0);
//				if(map.get("is_group").equals("0")){
//					dam = new DataAccessManager();
//					StringBuffer sql2 = new StringBuffer();
//					sql2.append("SELECT c.*  \n");
//					sql2.append("FROM   customers c  \n");
//					sql2.append("       INNER JOIN token_customer t  \n");
//					sql2.append("               ON c.customer_id = t.related_to  \n");
//					sql2.append("WHERE  c.customer_id = :custId  \n");
//
//					dam.setQueryString(sql2.toString());
//					dam.setObject("custId", custId);
//					List<Map<String,Object>> list2 = dam.exeQuery();
//					if(!list2.isEmpty()){
//						return "已配發過，請重新輸入正確客戶ID";
////						break;
//					}
//					dam = new DataAccessManager();
//					sql = new StringBuffer();
//					sql.append("insert into Token_Customer (token_esn , related_to ,is_related_to_group) ");
//					sql.append("values (:token_esn , :custId, '0')");
//					dam.setQueryString(sql.toString());
//					dam.setObject("token_esn", token_esn);
//					dam.setObject("custId", custId);
//					dam.exeUpdate();
//				}else if(map.get("is_group").equals("1") && map.get("is_group_main").equals("1")){
//					dam = new DataAccessManager();
//					StringBuffer sql2 = new StringBuffer();
//					sql2.append("SELECT *  \n");
//					sql2.append("FROM   customers c  \n");
//					sql2.append("       INNER JOIN token_customer tc  \n");
//					sql2.append("               ON c.group_id = tc.related_to  \n");
//					sql2.append("WHERE  c.customer_id = :custId  \n");
//					dam.setQueryString(sql2.toString());
//					dam.setObject("custId", custId);
//					List<Map<String,Object>> list2 = dam.exeQuery();
//					if(!list2.isEmpty()){
//						return "已配發過或不為主帳戶，請重新輸入正確客戶ID";
////						break;
//					}
//					String group_id = (String)map.get("group_id");
//					dam = new DataAccessManager();
//					sql = new StringBuffer();
//					sql.append("insert into Token_Customer (token_esn , related_to ,is_related_to_group) ");
//					sql.append("values (:token_esn , :group_id, '1')");
//					dam.setQueryString(sql.toString());
//					dam.setObject("token_esn", token_esn);
//					dam.setObject("group_id", group_id);
//					dam.exeUpdate();
//				}else{
//					return "已配發過或不為主帳戶，請重新輸入正確客戶ID";
//				}
////					dam = new DataAccessManager();
////					sql = new StringBuffer();
////					sql.append("select * from Customers where customer_id = :custId ");
////					sql.append("and otp_status in ('00','03') ");
////					dam.setQueryString(sql.toString());
////					dam.setObject("custId", custId);
////					break;
////				}
//			}else{
//				return "不存在，請重新輸入正確客戶ID";
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//			message = e.getMessage();
//		}
//		return message;
//	}
	
	/**
	 * 寫入Job_queues
	 * @param customer_id
	 * @param name
	 * @param phone
	 * @param address
	 * @return
	 */
	private Boolean insertJobQs(String custId,String token_esn,String userName,String job_code,String token_status){
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO Job_queues(job_id, job_code, customer_id, "+ 
					 " token_esn, token_status, operator, submitted_time) " +
	                 "VALUES(:job_id, :job_code, :customer_id, "+ 
					 " :token_esn, :token_status, :operator, getdate())";
		dam.setQueryString(sql);
		dam.setObject("job_id", IdUtil.getJob_id());
		dam.setObject("job_code", job_code);
		dam.setObject("customer_id", custId);
		dam.setObject("token_esn", token_esn);
		dam.setObject("token_status", token_status);
		dam.setObject("operator", userName);
		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 寫入Job_queues (集團戶)
	 * @param customer_id
	 * @param name
	 * @param phone
	 * @param address
	 * @return
	 */
	private Boolean insertJobQs_group(String custId,String token_esn,String userName,String job_code,String token_status,String group_id){
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO Job_queues(job_id, job_code, customer_id,group_id, "+ 
				" token_esn, token_status, operator, submitted_time) " +
				"VALUES(:job_id, :job_code, :customer_id, "+ 
				":group_id, :token_esn, :token_status, :operator, getdate())";
		dam.setQueryString(sql);
		dam.setObject("job_id", IdUtil.getJob_id());
		dam.setObject("job_code", job_code);
		dam.setObject("customer_id", custId);
		dam.setObject("group_id", group_id);
		dam.setObject("token_esn", token_esn);
		dam.setObject("token_status", token_status);
		dam.setObject("operator", userName);
		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
}
